package com.emm.yixun.website.controller.marketing;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PushbackInputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.emm.yixun.common.model.enums.RoomStatusEnum;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.emm.yixun.common.model.dto.PriceSchemeView;
import com.emm.yixun.common.response.ResponseList;
import com.emm.yixun.common.utils.MathUtil;
import com.emm.yixun.common.utils.SequencesGenerator;
import com.emm.yixun.marketing.service.PriceSchemeServiceFacade;
import com.emm.yixun.website.controller.base.BaseController;
import com.emm.yixun.website.task.utils.ExcelReader;
import com.emm.yixun.website.vo.common.ExtResultVo;

/**
* 类描述： EXCEL处理类
* 创建者： ThinkPad
* 项目名称： emm-website
* 创建时间： 2016年8月8日 上午10:58:21
* 版本号： v1.0
 */
@RequestMapping(value = "excel")
@Controller
public class ExcelController extends BaseController<Object>{
	/**
	* 方法描述 : 动态生成EXCEL
	* 创建者：ThinkPad 
	* 项目名称： emm-website
	* 类名： ExcelController.java
	* 版本： v1.0
	* 创建时间： 2016年8月8日 上午11:21:10
	* @param request
	* @param treeId
	* @param response void
	 */
	@Autowired
	private PriceSchemeServiceFacade priceSchemeServiceFacade;
	@RequestMapping(value = "downloadExcelModel")
	public void downloadExcelModel(HttpServletRequest request,String treeId,HttpServletResponse response,String floorType, String noTradeHouse){
		try {
			  @SuppressWarnings("resource")
			  HSSFWorkbook wb = new HSSFWorkbook();  
			  HSSFSheet sheet = wb.createSheet("价格列表");  
			  HSSFCellStyle headStyle = wb.createCellStyle(); 
			  HSSFFont f  = wb.createFont();      
			  f.setFontHeightInPoints((short) 11);// 字号   
		      f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗   
		      headStyle.setFont(f); 
		      headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
		      headStyle.setBorderBottom((short) 1);
		      headStyle.setBorderRight((short) 1);
		      headStyle.setFillBackgroundColor((short) 20);
		      HSSFRow row = sheet.createRow((int) 0); 
		      //创建单元格，并设置值表头 设置表头居中  
		      HSSFCellStyle style = wb.createCellStyle();  
		      style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
		      style.setBorderBottom((short) 1);
		      style.setBorderRight((short) 1);
		      String changeField = null;
		      if(floorType.equals("0")){
		    	  changeField="底价";
		      }else if(floorType.equals("1")){
		    	  changeField="折扣比例";
		      }
		      String[] header = new String[]{"房源编号","房号","建筑面积","套内面积","表单价","表总价",changeField,"浮动范围"};
		        for (int i = 0; i < header.length; i++)  {  
		            HSSFCell cell = row.createCell(i);  
		            cell.setCellStyle(style);  
		            HSSFRichTextString text = new HSSFRichTextString(header[i]);  
		            cell.setCellValue(text);  
		        }  
		      HSSFCell cell = null;
		      List<PriceSchemeView> result =  excelData(treeId);
		      int rowId = 1;
			  for (int j = 0; j < result.size(); j++) {
				  PriceSchemeView price = (PriceSchemeView) result.get(j);
				  //导出无交易状态房源
				  // 非认筹、认购、签约房源
				  if ("1".equals(noTradeHouse)) {
					String roomStatus = price.getRoomStatus();
				  	if(StringUtils.isNotBlank(roomStatus)){
				  		if(RoomStatusEnum.RESERVE.getNo().equals(roomStatus)
							|| RoomStatusEnum.BUY.getNo().equals(roomStatus)
							|| RoomStatusEnum.SIGN.getNo().equals(roomStatus)){
							continue;
						}
					}
				  }
				  row = sheet.createRow(rowId);
				  cell = row.createCell(0,1);
				  cell.setCellValue(price.getId());
				  cell = row.createCell(1,1);
				  cell.setCellValue(price.getComboxText());
				  cell = row.createCell(2,1);
				  cell.setCellValue(price.getBuildingArea().toString());
				  cell = row.createCell(3,1);
				  if(price.getInnerArea()!=null){
					  cell.setCellValue(price.getInnerArea().toString());
				  }
				  cell = row.createCell(4,1);
				  cell.setCellValue("0");
				  if(null != price.getPrice()){
					  cell.setCellValue(price.getPrice().toString());
				  }
				  cell = row.createCell(5,1);
				  cell.setCellValue("0");
				  if(null != price.getTotalPrice()){
					  cell.setCellValue(price.getTotalPrice().toString());
				  }
				  cell = row.createCell(6,1);
				  cell.setCellValue("0");
				  if(null != price.getDiscountRate()){
					  cell.setCellValue(price.getDiscountRate().toString());
				  }
				  cell = row.createCell(7,1);
				  cell.setCellValue("0");
				  if(null != price.getFloatingRange()){
					  cell.setCellValue(price.getFloatingRange().toString());
				  }
				  rowId++;
			}
		      SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		      String mimetype = "application/x-msdownload";
		      String downFileName = "excel-"+df.format(new Date())+".xls";
		      String inlineType = "attachment"; 
		      response.setHeader("Content-Disposition", inlineType  + ";filename=\"" + downFileName + "\"");
		      OutputStream out=response.getOutputStream();
		      response.setContentType(mimetype);
		      wb.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	* 方法描述 : 导入模版数据
	* 创建者：ThinkPad 
	* 项目名称： emm-website
	* 类名： ExcelController.java
	* 版本： v1.0
	* 创建时间： 2016年8月8日 下午3:06:26
	* @param request
	* @param response
	* @param file
	* @return AjaxRes
	 */
	@RequestMapping(value = "importModel")
	public @ResponseBody ExtResultVo importModel(HttpServletRequest request, HttpServletResponse response, MultipartFile file){
		ExtResultVo res = new ExtResultVo();
		try {
			//解析excel
			String no = SequencesGenerator.getUUID("BTN");
			Workbook wb = create(file.getInputStream());
			Sheet xssfSheet = wb.getSheetAt(0);
			List<Object> list = getExcelValue(xssfSheet, new PriceSchemeView());
			request.getSession().setAttribute(no, list);
			res.setData(no);
			res.setSuccess(true);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return res;
	}
	/**
	* 方法描述 : 从seession中获取导入数据
	* 创建者：ThinkPad 
	* 项目名称： emm-website
	* 类名： ExcelController.java
	* 版本： v1.0
	* 创建时间： 2016年8月8日 下午4:01:00
	* @param request
	* @param no
	* @return List<PriceSchemeView>
	 */
	@RequestMapping(value = "refreshGrid")
	public @ResponseBody List<PriceSchemeView> refreshGrid(HttpServletRequest request,String no){
		Object obj = request.getSession().getAttribute(no);
		if(obj!=null){
			@SuppressWarnings("unchecked")
			List<PriceSchemeView> result = (List<PriceSchemeView>)obj;
			return result; 
		}
		return new ArrayList<PriceSchemeView>();
	}
 	
	public static Workbook create(InputStream in) throws IOException,InvalidFormatException {
        if (!in.markSupported()) {
            in = new PushbackInputStream(in, 8);
        }
        if (POIFSFileSystem.hasPOIFSHeader(in)) {
            return new HSSFWorkbook(in);
        }
        if (POIXMLDocument.hasOOXMLHeader(in)) {
            return new XSSFWorkbook(OPCPackage.open(in));
        }
        throw new IllegalArgumentException("你的excel版本目前poi解析不了");
    }
	
	
	//需要生成EXCEL的数据
	public List<PriceSchemeView> excelData(String treeId){
		ResponseList<PriceSchemeView> responseList= priceSchemeServiceFacade.findRoomList(treeId, null);
			return responseList.getEntity();
	}

	public List<Object> getExcelValue(Sheet xssfSheet,Object model) throws Exception{
		List<Object> arraylist = new ArrayList<Object>();
		//获取总行数
		Class<?> tClass = model.getClass();
		Field[] fields = tClass.getDeclaredFields();
		Integer totalRow = xssfSheet.getPhysicalNumberOfRows();
		for (int i = 1; i < totalRow; i++) {
			//System.out.println(i);
			Integer totalCell=(int)xssfSheet.getRow(i).getLastCellNum();
			for (int j = 0; j < totalCell; j++) {  //实体对象的字段值对应EXCEL列数
				String name = fields[j].getName(); // 获取属性的名字
				name = name.substring(0, 1).toUpperCase() + name.substring(1); // 将属性的首字符大写，方便构造get，set
				if(name.equals("innerPrice")){
					continue;
				}
				
				Method m = model.getClass().getMethod("get" + name);
				
				Object objValue =ExcelReader.getCell(xssfSheet.getRow(i).getCell(j)) ;
				String type = fields[j].getGenericType().toString();
				if(type.equals("class java.math.BigDecimal")){
					m = model.getClass().getMethod("set" + name,BigDecimal.class);
					if(StringUtils.isNotEmpty(objValue.toString())){
						m.invoke(model,objValue==null?null:new BigDecimal(MathUtil.round(new BigDecimal(objValue.toString()), 2)));
					}else{
						m.invoke(model,objValue==null?null:new BigDecimal(MathUtil.round(new BigDecimal(0), 2)));
					}
				}else{
					m = model.getClass().getMethod("set" + name,String.class);
					m.invoke(model,objValue==null?null:objValue.toString());
				}
			}
			if(model instanceof PriceSchemeView){
				arraylist.add(model);
				model=new PriceSchemeView();
			}
			
		}
		return arraylist;
	}
}
